import json
import math
from datetime import datetime
import torch
import torch.nn as nn #导入nn，nn.linear是一个全连接层
from transformers import BertModel, BertTokenizer# 导入bert模型和bert的分词器
from django.http import HttpResponse
from django.shortcuts import render
from py2neo import Graph, Node, Relationship, NodeMatcher
import pymysql.cursors
# 连接数据库
connect = pymysql.Connect(
    host='localhost',
    port=3306,
    user='root',
    passwd='123456',
    db='qa_system',
    charset='utf8'
)
# 获取游标
cursor = connect.cursor()

# 视图函数
# 首页
def index(request):
    return render(request,"index.html")
# 登录
def login(request):
    if (request.method == 'GET'):
        return render(request,"login.html")
    if (request.method == 'POST'):
        username = request.POST.get("username")
        password = request.POST.get("password")
        re = {'code': 200, 'msg': 1}
        sql = "SELECT * FROM user WHERE u_name='%s' "
        data = (username,)  # 元组中只有一个元素的时候需要加一个逗号
        cursor.execute(sql % data)
        if (cursor.rowcount == 0):
            re['msg'] = -1
        else:
            user=cursor.fetchone()
            if(user[2]!=password):
                re['msg'] = 0
        return HttpResponse(json.dumps(re))
# 注册
def register(request):
    if(request.method=='GET'):
        return render(request,"register.html")
    if(request.method == 'POST'):
        # 查询注册账户是否已经存在
        username=request.POST.get("username")
        password=request.POST.get("password")
        re = {'code': 200, 'msg':1}
        sql = "SELECT * FROM user WHERE u_name='%s' "
        data = (username,)  # 元组中只有一个元素的时候需要加一个逗号
        cursor.execute(sql % data)
        if(cursor.rowcount==0):
            sql = "INSERT INTO user(u_name,u_pwd) VALUES('%s','%s')"
            data = (username, password)
            cursor.execute(sql % data)
            connect.commit()
        else:
            re['msg']=0
        return HttpResponse(json.dumps(re))
# 修改密码
def pwd(request):
    username = request.POST.get("username")
    old_pwd = request.POST.get("old_pwd")
    new_pwd = request.POST.get("new_pwd")
    re = {'code': 200, 'msg': 1}
    sql = "SELECT * FROM user WHERE u_name='%s' "
    data = (username,)  # 元组中只有一个元素的时候需要加一个逗号
    cursor.execute(sql % data)
    user = cursor.fetchone()
    if (user[2] != old_pwd):
        re['msg'] = 0
    else:
        sql = "UPDATE user SET u_pwd='%s' WHERE u_name='%s'"
        data = (new_pwd, username)
        cursor.execute(sql % data)
        connect.commit()
    return HttpResponse(json.dumps(re))
# 自动问答
def qa(request):
    if (request.method == 'GET'):
        return render(request,"qa.html")
    if (request.method == 'POST'):
        username = request.POST.get("username")
        question= request.POST.get("question")
        re = {'code': 200, 'msg': 1,'type':'','type_num':-1,'ner':[],'entity':'','answer':'','time':''}
        # 问题分类
        bertclassfication = torch.load(r'D:\study\毕设\model\bertclassfication_0.93.pt')
        transform_dict = {0: '病害', 1: '虫害', 2: '英文名', 3: '别名', 4: '简介', 5: '为害症状', 6: '病原物',
                          7: '侵染循环', 8: '发生因素', 9: '形态特征', 10: '生活习性', 11: '防治方法', 12: '易患杂豆', 13: '易患杂豆', 14: '所致病害'}
        type = bertclassfication([question])
        _, type = torch.max(type, 1)
        re['type']=transform_dict[type.tolist()[0]]
        re['type_num']=type.tolist()[0]
        # 命名实体识别
        model = torch.load(r'D:\study\毕设\model\ner.pt')
        ix_to_tag = {0: "B-BEAN", 1: "I-BEAN", 2: "B-DISEASE", 3: "I-DISEASE", 4: "B-INSECT", 5: "I-INSECT",
                     6: "B-PATHONGEN", 7: "I-PATHONGEN", 8: "O"}
        word_list=list(question)
        ner_result = model(word_list)
        for i in ner_result:
            re['ner'].append(i[0])
        for i in range(len(word_list)):
            if(re['ner'][i]!=8):
                re['entity']+=word_list[i]
        # 连接neo4j数据库，查询问题答案
        graph = Graph("http://localhost:7474", auth=("neo4j", "zwa20010718"))
        if(type.tolist()[0]==0): # 杂豆病害类问题
            search_result=graph.run("MATCH(n:杂豆)-[r:易患1]->(nn:病害) WHERE n.name = '"+re['entity']+"' RETURN nn.CN").data()
            for i in search_result:
                re['answer']+=i['nn.CN']+'、'
            re['answer'] = re['answer'][:-1]
        elif(type.tolist()[0]==1): # 杂豆虫害类问题
            search_result = graph.run("MATCH(n:杂豆)-[r:易患2]->(nn:虫害) WHERE n.name = '"+re['entity']+"' RETURN nn.CN").data()
            for i in search_result:
                re['answer'] += i['nn.CN'] + '、'
            re['answer'] = re['answer'][:-1]
        elif (type.tolist()[0] == 2):  # 英文名类问题
            search_result = graph.run("MATCH(n) WHERE n.CN = '"+re['entity']+"' RETURN n.EN").data()
            if (search_result != []):
                re['answer'] = search_result[0]['n.EN']
            else:
                re['answer']='wrong'
        elif (type.tolist()[0] == 3):  # 别名类问题
            search_result = graph.run("MATCH(n) WHERE n.CN = '"+re['entity']+"' RETURN n.AN").data()
            if (search_result != []):
                re['answer'] = search_result[0]['n.AN']
            else:
                re['answer']='wrong'
        elif (type.tolist()[0] == 4):  # 简介类问题
            search_result = graph.run("MATCH(n) WHERE n.CN = '"+re['entity']+"' RETURN n.Brief").data()
            if (search_result != []):
                re['answer'] = search_result[0]['n.Brief']
            else:
                re['answer'] = 'wrong'
        elif (type.tolist()[0] == 5):  # 为害症状类问题
            search_result = graph.run("MATCH(n) WHERE n.CN = '"+re['entity']+"' RETURN n.Symptom").data()
            if (search_result != []):
                re['answer'] = search_result[0]['n.Symptom']
            else:
                re['answer'] = 'wrong'
        elif (type.tolist()[0] == 6):  # 病原物类问题
            search_result = graph.run("MATCH(n:病原物)-[r:导致]->(nn:病害) WHERE nn.CN = '" + re['entity'] + "' RETURN n.CN").data()
            for i in search_result:
                re['answer'] += i['n.CN'] + '、'
            re['answer'] = re['answer'][:-1]
        elif (type.tolist()[0] == 7):  # 侵染循环类问题
            search_result = graph.run("MATCH(n:病原物) WHERE n.CN = '"+re['entity']+"' RETURN n.Infect").data()
            if (search_result != []):
                re['answer'] = search_result[0]['n.Infect']
            else:
                re['answer'] = 'wrong'
        elif (type.tolist()[0] == 8):  # 发生因素类问题
            search_result = graph.run("MATCH(n) WHERE n.CN = '"+re['entity']+"' RETURN n.Reason").data()
            if(search_result!=[]):
                re['answer'] = search_result[0]['n.Reason']
            else:
                re['answer'] = 'wrong'
        elif (type.tolist()[0] == 9):  # 形态特征类问题
            search_result = graph.run("MATCH(n:虫害) WHERE n.CN = '"+re['entity']+"' RETURN n.Feature").data()
            if(search_result!=[]):
                re['answer'] = search_result[0]['n.Feature']
            else:
                re['answer'] ='wrong'
        elif (type.tolist()[0] == 10):  # 生活习性类问题
            search_result = graph.run("MATCH(n:虫害) WHERE n.CN = '" + re['entity'] + "' RETURN n.Lifestyle").data()
            if(search_result!=[]):
                re['answer'] = search_result[0]['n.Lifestyle']
            else:
                re['answer'] ='wrong'
        elif (type.tolist()[0] == 11):  # 防治方法类问题
            search_result = graph.run("MATCH(n) WHERE n.CN = '" + re['entity'] + "' RETURN n.Method").data()
            if (search_result != []):
                re['answer'] = search_result[0]['n.Method']
            else:
                re['answer'] = 'wrong'
        elif(type.tolist()[0]==12): # 患病害杂豆类问题
            search_result = graph.run("MATCH(n:杂豆)-[r:易患1]->(nn:病害) WHERE nn.CN = '"+re['entity']+"' RETURN n.name").data()
            for i in search_result:
                re['answer'] += i['n.name'] + '、'
            re['answer'] = re['answer'][:-1]
        elif (type.tolist()[0] == 13):  # 患虫害杂豆类问题
            search_result = graph.run(
                "MATCH(n:杂豆)-[r:易患2]->(nn:虫害) WHERE nn.CN = '" + re['entity'] + "' RETURN n.name").data()
            for i in search_result:
                re['answer'] += i['n.name'] + '、'
            re['answer'] = re['answer'][:-1]
        elif (type.tolist()[0] == 14):  # 致病病害类问题
            search_result = graph.run(
                "MATCH(n:病原物)-[r:导致]->(nn:病害) WHERE n.CN = '"+re['entity']+"' RETURN nn.CN").data()
            for i in search_result:
                re['answer'] += i['nn.CN'] + '、'
            re['answer'] = re['answer'][:-1]
        if(re['answer']==''):
            re['msg']= -1 # 缺失值
        elif (re['answer'] == 'wrong'):
            re['msg'] = 0 # 问题分类错误或实体识别错误
        else:
            sql = "SELECT * FROM question WHERE content='%s' "
            data = (question,)
            cursor.execute(sql % data)
            if (cursor.rowcount == 0):
                sql = "INSERT INTO question(content,answer,type,num) VALUES('%s','%s','%d','%d')"
                data = (question,re['answer'],type.tolist()[0],1)
                cursor.execute(sql % data)
                connect.commit()
            else:
                obj = cursor.fetchone()
                q_id = obj[0]
                num = obj[4]+1
                sql = "UPDATE question SET num='%d' WHERE q_id='%d'"
                data = (num,q_id)
                cursor.execute(sql % data)
                connect.commit()
            sql = "SELECT u_id FROM user WHERE u_name='%s' "
            data = (username,)
            cursor.execute(sql % data)
            u_id=cursor.fetchone()[0]
            sql = "SELECT q_id FROM question WHERE content='%s' "
            data = (question,)
            cursor.execute(sql % data)
            q_id = cursor.fetchone()[0]
            time = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
            re['time']=time
            sql = "INSERT INTO ask VALUES('%d','%d','%s','%d')"
            data = (u_id, q_id,time,0)
            cursor.execute(sql % data)
            connect.commit()
        print(type.tolist()[0])
        print(re['entity'])
        return HttpResponse(json.dumps(re))
# 猜你想问
def after(o,Lnum):
    o1=[]
    sum=0
    for i in o:
        obj=math.pow(i,2)
        o1.append(obj)
        sum+=obj
    k=math.sqrt(sum)
    for i in range(Lnum):
        o1[i]=o[i]/k
    # 计算概率矩阵p
    sum=0
    for i in o1:
        sum+=i
    for i in o1:
        i=i/sum
    # 计算熵权
    a=-1/(math.log(Lnum))
    sum=0
    for i in o1:
        if(i==0):
            sum+=0
        else:
            sum+=i*math.log(abs(i))
    sum=sum*a
    sum=1-sum
    return sum
def finalre(o1,o2):
    finalS=[]
    a=o1+o2
    if(a==0):
        print("两个都是0")
    else:
        finalS.append(o1/a)
        finalS.append(o2/a)
    return finalS
def standardization(a):
    maxN=max(a)
    minN=min(a)
    if(maxN!=0):
        for i in range(len(a)):
            a[i]=(a[i]-minN)/(maxN-minN)
    return a
def recommend(request):
    if (request.method == 'GET'):
        return render(request,"recommend.html")
    if (request.method == 'POST'):
        username = request.POST.get("username")
        pageSize = int(request.POST.get("pageSize"))
        maxPage = int(request.POST.get("maxPage"))
        currentPage = int(request.POST.get("currentPage"))
        transform_dict = {0: '病害', 1: '虫害', 2: '英文名', 3: '别名', 4: '简介', 5: '为害症状', 6: '病原物',
                          7: '侵染循环', 8: '发生因素', 9: '形态特征', 10: '生活习性', 11: '防治方法', 12: '易患杂豆', 13: '易患杂豆', 14: '所致病害'}
        re = {'code': 200, 'msg': 1, 'data':[],'pageNum':0}
        # 获取其他用户提的所有问题
        sql = "SELECT * FROM question JOIN ask ON question.q_id=ask.q_id JOIN user ON ask.u_id=user.u_id WHERE u_name!='%s' ORDER BY num desc"
        data = (username,)
        cursor.execute(sql % data)
        all_question=cursor.fetchall()
        # 去除重复问题
        all_question_new=[]
        for i in all_question:
            flag=0
            for j in all_question_new:
                if(i[1]==j[1]):
                    flag=1
                    all_question_new.remove(j)
                    all_question_new.append(i)
            if(flag==0):
                all_question_new.append(i)
        # 获取用户收藏列表
        sql = "SELECT * FROM `like` JOIN `user` ON `user`.u_id=`like`.u_id JOIN question ON `like`.q_id=question.q_id JOIN ask ON ask.q_id=question.q_id WHERE u_name='%s'"
        data = (username,)
        cursor.execute(sql % data)
        likeList=cursor.fetchall()
        # 去除用户收藏过的问题
        initList=[]
        for i in all_question_new:
            flag=0;
            for j in likeList:
                if(i[1]==j[6]):
                    flag=1;
                    break;
            if(flag==0):
                initList.append(i)
        num=[]
        flag=[]
        for i in initList:
            num.append(i[4])
            sql = "SELECT * FROM ask WHERE q_id='%d'"
            data = (i[0],)
            cursor.execute(sql % data)
            flagList=cursor.fetchall()
            sum=0
            for j in flagList:
               sum+=j[3]
            flag.append(sum)
        # 基于流行度的推荐
        sum1 = after(num, len(initList))
        sum2 = after(flag, len(initList))
        finalS = finalre(sum1, sum2)
        score1=[]
        for i in range(len(initList)):
            score1.append(finalS[0]*num[i]+finalS[1]*flag[i])
        score1=standardization(score1)
        # 基于用户的协同过滤算法
        arr_to_cos=[] # 用户-问题矩阵
        arr_user=[] # 存储余弦相似度
        userListNum=[] # 存储用户收藏的问题总数
        sql = "SELECT * FROM user"
        cursor.execute(sql)
        query_user = cursor.fetchall()
        sql = "SELECT * FROM question"
        cursor.execute(sql)
        query_question = cursor.fetchall()
        for row in query_user:
            arr=[]
            arr2=[]
            for i in range(len(query_question)):
                arr.append(0)
                arr2.append(0)
            obj={row[0]:arr}
            obj2={row[0]: arr2}
            arr_to_cos.append(obj)
            arr_user.append(obj2)
        sql = "SELECT * FROM `like`"
        cursor.execute(sql)
        query_like = cursor.fetchall()
        for row in query_like:
            for j in range(len(arr_to_cos)):
                if(row[0]==list(arr_to_cos[j].keys())[0]):
                    arr_to_cos[j][row[0]][row[1]]=1
                    break
        for i in query_user:  # 遍历每一个用户，计算用户收藏问题总数，存入userListNum
            num=0
            for j in query_like:
                if(i[0]==j[0]):
                    num+=1
            obj = {i[0]: num}
            userListNum.append(obj)
        for i in range(len(arr_to_cos)):
            for j in range(len(arr_to_cos)):
                uUid=list(arr_to_cos[i].keys())[0] # 用户i的u_id
                oUid=list(arr_to_cos[j].keys())[0] # 用户j的u_id
                user=list(arr_to_cos[i].values())[0] # 用户i收藏的问题
                other=list(arr_to_cos[j].values())[0] # 用户j收藏的问题
                if(uUid==oUid):
                    continue
                num=0
                for k in range(len(user)):
                    if(user[k]==1 and other[k]==1): # 用户A和用户B共同收藏的问题
                        num+=1
                uNum=0
                oNum=0
                for k in range(len(userListNum)):
                    if(list(userListNum[k].keys())[0]==uUid):
                        uNum=list(userListNum[k].values())[0]
                        continue
                    if (list(userListNum[k].keys())[0] == oUid):
                        oNum = list(userListNum[k].values())[0]
                if(uNum*oNum!=0):
                    arr_user[i][uUid][oUid]=num/math.sqrt(uNum*oNum)
                else:
                    arr_user[i][uUid][oUid]=0
        sql = "SELECT * FROM user WHERE u_name='%s'"
        data = (username,)
        cursor.execute(sql % data)
        thisUser=cursor.fetchall()[0]
        userCos=[] # 当前用户与其他用户的余弦相似度
        for i in arr_user:
            if(thisUser[0]==list(i.keys())[0]):
                userCos=list(i.values())[0]
        userNum=0
        similarUser=[] #余弦相似度大于0的用户
        for i in range(len(userCos)):
            if(userCos[i]>0):
                userNum+=1
                u={'index':i,'value':userCos[i]}
                similarUser.append(u)
        if(userNum>5):
            userNum=5
        similarUser.sort(key=lambda i: i["index"],reverse=True) # 按照余弦相似度降序排列
        max_K=similarUser[0:userNum] #选出相似度最高的K(5)个用户
        max_K_List=[] # 存储k个用户的收藏列表
        for i in max_K:
            oUid=-1
            for j in range(len(arr_to_cos)):
                if(i['index']==list(arr_to_cos[j].keys())[0]):
                    oUid=j
                    break
            oList=list(arr_to_cos[oUid].values())[0]
            for j in range(len(oList)):
                if(oList[j]==1):
                    sql = "SELECT * FROM `like` WHERE q_id='%d'"
                    data = (j,)
                    cursor.execute(sql % data)
                    result=cursor.fetchall()
                    s={'index':j,'score':len(result)*i['value']} # 问题被收藏的次数*余弦相似度
                    max_K_List.append(s)
        max_K_List2=[] # 去除重复值
        for i in max_K_List:
            flag2=0
            for j in max_K_List2:
                if(i['index']==j['index']):
                    flag2=1 # 重复
                    if(j['score']>i['score']):
                        try:
                            max_K_List2.remove(i)
                        except ValueError as e:
                            print(i)
                            print(j)
                        max_K_List2.append(j)
                    break
            if(flag2==0):
                max_K_List2.append(i)
        score2=[]
        for i in initList:
            flag3=0
            for j in max_K_List2:
                if(i[0]==j['index']):
                    flag3=1
                    break
            if(flag3==1):
                score2.append(j['score'])
            else:
                score2.append(0)
        score2 = standardization(score2)
        w1=w2=0.5
        for i in range(len(initList)):
            initList[i]+=(round(score1[i]*w1+score2[i]*w2,2),)
        finalList=[]
        for i in range(len(initList)):
            for j in range(len(initList)-1):
                if(initList[i][12]>initList[j][12]):
                    t=initList[j]
                    initList[j]=initList[i]
                    initList[i]=t
        for i in initList:
            if(i[12]!=0.0):
                finalList.append(i)
        if(len(finalList)<=50):
            finalList=finalList
        else:
            finalList=finalList[0:50]
        re['pageNum'] = math.ceil(len(finalList) / pageSize)
        if(re['pageNum']>maxPage):
            re['pageNum']=maxPage
        start = 0
        end = 0
        if(currentPage!= re['pageNum']):
            start=(currentPage-1)*pageSize
            end= currentPage*pageSize
        else:
            start = (currentPage - 1) * pageSize
            end = len(finalList)
        for i in range(start,end):
            item={}
            item['q_id'],item['content'],item['answer'],item['type'],item['num'],item['u_id'],_,item['time'],_,_,_,_,item['score']= finalList[i]
            item['type']=transform_dict[item['type']]
            re['data'].append(item)
        if(re['data']==[]):
            re['msg']=0
        return HttpResponse(json.dumps(re))
# 收藏&取消收藏
def like(request):
    if (request.method == 'POST'):
        username = request.POST.get("username")
        question = request.POST.get("ask")
        type = request.POST.get("type")
        re = {'code': 200, 'msg': 1}
        sql = "SELECT u_id FROM user WHERE u_name='%s' "
        data = (username,)
        cursor.execute(sql % data)
        u_id = cursor.fetchone()[0]
        sql = "SELECT q_id FROM question WHERE content='%s' "
        data = (question,)
        cursor.execute(sql % data)
        q_id = cursor.fetchone()[0]
        sql = "SELECT * FROM `like` WHERE u_id='%d' and q_id='%d'"
        data = (u_id,q_id)
        cursor.execute(sql % data)
        if(type=='addLike'):
            if (cursor.rowcount == 0):
                sql = "INSERT INTO `like` VALUES('%d','%d')"
                data = (u_id, q_id)
                cursor.execute(sql % data)
                connect.commit()
            else:
                re['msg']=0
        elif(type=='removeLike'):
            if (cursor.rowcount != 0):
                sql = "DELETE FROM `like` WHERE u_id='%d' and q_id='%d'"
                data = (u_id, q_id)
                cursor.execute(sql % data)
                connect.commit()
            else:
                re['msg']=0
        return HttpResponse(json.dumps(re))
# 我的收藏
def myLike(request):
    if (request.method == 'POST'):
        username = request.POST.get("username")
        type = int(request.POST.get("type"))
        re = {'code': 200, 'msg': 1,'data':[]}
        sql = "SELECT * FROM `like` JOIN `user` ON `user`.u_id=`like`.u_id JOIN question ON `like`.q_id=question.q_id JOIN ask ON ask.q_id=question.q_id WHERE u_name='%s' ORDER BY time "
        if(type==1):
            sql+="DESC" # 降序排列
        data = (username,)
        cursor.execute(sql % data)
        result = cursor.fetchall()
        # 去除重复问题
        result_new = []
        for i in result:
            flag = 0
            for j in result_new:
                if (i[1] == j[1]):
                    flag = 1
                    result_new.remove(j)
                    result_new.append(i)
            if (flag == 0):
                result_new.append(i)
        transform_dict = {0: '病害', 1: '虫害', 2: '英文名', 3: '别名', 4: '简介', 5: '为害症状', 6: '病原物',
                          7: '侵染循环', 8: '发生因素', 9: '形态特征', 10: '生活习性', 11: '防治方法', 12: '易患杂豆', 13: '易患杂豆', 14: '所致病害'}
        if(len(result_new)!=0):
            for i in range(len(result_new)):
                item = {}
                item['u_id'], item['q_id'],_,_,_,_,item['content'],item['answer'],item['type'],item['num'],_,_,item['time'],_= result_new[i]
                item['type'] = transform_dict[item['type']]
                re['data'].append(item)
        else:
            re['msg'] = 0
        return HttpResponse(json.dumps(re))
# 我的提问
def myQuestion(request):
    if (request.method == 'POST'):
        username = request.POST.get("username")
        type = int(request.POST.get("type"))
        re = {'code': 200, 'msg': 1,'data':[]}
        sql = "SELECT * FROM ask JOIN question ON ask.q_id=question.q_id JOIN `user` ON `user`.u_id=ask.u_id WHERE u_name='%s' ORDER BY time "
        if(type==1):
            sql+="DESC" # 降序排列
        data = (username,)
        cursor.execute(sql % data)
        result = cursor.fetchall()
        transform_dict = {0: '病害', 1: '虫害', 2: '英文名', 3: '别名', 4: '简介', 5: '为害症状', 6: '病原物',
                          7: '侵染循环', 8: '发生因素', 9: '形态特征', 10: '生活习性', 11: '防治方法', 12: '易患杂豆', 13: '易患杂豆', 14: '所致病害'}
        if(cursor.rowcount!=0):
            for i in range(cursor.rowcount):
                item = {}
                item['u_id'], item['q_id'],item['time'],item['flag'],_,item['content'],item['answer'],item['type'],item['num'],_,_,_= result[i]
                item['type'] = transform_dict[item['type']]
                re['data'].append(item)
        else:
            re['msg'] = 0
        return HttpResponse(json.dumps(re))
# 删除提问
def delQuestion(request):
    if (request.method == 'POST'):
        username = request.POST.get("username")
        question = request.POST.get("ask")
        time = request.POST.get("time")
        type = int(request.POST.get("type"))
        re = {'code': 200, 'msg': 1}
        sql = "SELECT u_id FROM user WHERE u_name='%s' "
        data = (username,)
        cursor.execute(sql % data)
        u_id = cursor.fetchone()[0]
        if (type == 0): # 删除单条提问记录
            sql = "SELECT q_id FROM question WHERE content='%s' "
            data = (question,)
            cursor.execute(sql % data)
            q_id = cursor.fetchone()[0]
            sql = "SELECT * FROM ask WHERE u_id='%d' and q_id='%d' and time='%s'"
            data = (u_id, q_id,time)
            cursor.execute(sql % data)
            if (cursor.rowcount != 0):
                sql = "DELETE FROM ask WHERE u_id='%d' and q_id='%d' and time='%s'"
                data = (u_id, q_id,time)
                cursor.execute(sql % data)
                connect.commit()
            else:
                re['msg'] = 0
        elif(type==1): # 删除所有提问记录
            sql = "DELETE FROM ask WHERE u_id='%d'"
            data = (u_id,)
            cursor.execute(sql % data)
            connect.commit()
        return HttpResponse(json.dumps(re))

# 问题反馈
def feedback(request):
    if(request.method=='POST'):
        username = request.POST.get("username")
        question = request.POST.get("question")
        time = request.POST.get("time")
        flag = int(request.POST.get("flag"))
        re = {'code': 200, 'msg': 1}
        sql = "SELECT u_id FROM user WHERE u_name='%s' "
        data = (username,)
        cursor.execute(sql % data)
        u_id = cursor.fetchone()[0]
        sql = "SELECT q_id FROM question WHERE content='%s' "
        data = (question,)
        cursor.execute(sql % data)
        q_id = cursor.fetchone()[0]
        sql = "SELECT * FROM ask WHERE u_id='%d' and q_id='%d' and time='%s'"
        data = (u_id, q_id, time)
        cursor.execute(sql % data)
        if(cursor.rowcount!=0):
            sql = "UPDATE ask SET flag='%d' WHERE u_id='%d' and q_id='%d' and time='%s'"
            data = (flag,u_id, q_id, time)
            cursor.execute(sql % data)
            connect.commit()
        else:
            re['msg'] = 0
        return HttpResponse(json.dumps(re))
# 可视化
def charts(request):
    if (request.method == 'POST'):
        chart=request.POST.get("chart")
        if(chart=='r'):
            entity = request.POST.get("entity")
            type = int(request.POST.get("type_num"))
            re = {'code': 200, 'msg': 1,'data':{'nodes':[],'links':[],'categories':[]}}
            # 连接neo4j数据库，查询问题答案
            graph = Graph("http://localhost:7474", auth=("neo4j", "zwa20010718"))
            if(type==0):
                search_result = graph.run(
                    "MATCH(n:`杂豆`) where n.name = '"+entity+"' RETURN id(n) as id,properties(n) as properties").data()
                n={'id':'','name':'','type':'bean','symbolSize':70,'category':0}
                n['id']=search_result[0]['id']
                n['name']=search_result[0]['properties']['name']
                re['data']['nodes'].append(n)
                search_result2 = graph.run(
                "MATCH(n:`杂豆`)-[r:易患1]->(nn:`病害`) where n.name = '"+entity+"' RETURN id(nn) as id,properties(nn) as properties").data()
                for i in search_result2:
                    n = {'id': '', 'name': '','type':'disease', 'EN':'','AN':'','Brief':'','Symptom':'','Reason':'','Method':'','symbolSize': 50, 'category': 1}
                    n['id'] = i['id']
                    n['name'] = i['properties']['CN']
                    n['EN'] = i['properties']['EN']
                    n['AN'] = i['properties']['AN']
                    n['Brief'] = i['properties']['Brief']
                    n['Symptom'] = i['properties']['Symptom']
                    n['Reason'] = i['properties']['Reason']
                    n['Method'] = i['properties']['Method']
                    for key in n.keys():
                        if(n[key]==''):
                            n[key]='无'
                    re['data']['nodes'].append(n)
                for i in range(1,len(re['data']['nodes'])):
                    l = {'source': 0, 'target': 0, 'name': '易患1','type':'edge'}
                    l['target']=i
                    re['data']['links'].append(l)
                re['data']['categories'].append({'name':'杂豆'})
                re['data']['categories'].append({'name':'病害'})
            elif (type == 1):
                search_result = graph.run(
                    "MATCH(n:`杂豆`) where n.name = '" + entity + "' RETURN id(n) as id,properties(n) as properties").data()
                n = {'id': '', 'name': '', 'type': 'bean', 'symbolSize': 70, 'category': 0}
                n['id'] = search_result[0]['id']
                n['name'] = search_result[0]['properties']['name']
                re['data']['nodes'].append(n)
                search_result2 = graph.run(
                    "MATCH(n:`杂豆`)-[r:易患2]->(nn:`虫害`) where n.name = '" + entity + "' RETURN id(nn) as id,properties(nn) as properties").data()
                for i in search_result2:
                    n = {'id': '', 'name': '', 'type': 'insect', 'EN': '', 'AN': '', 'Brief': '', 'Symptom': '',
                         'Feature':'','Lifestyle':'','Reason': '', 'Method': '', 'symbolSize': 50, 'category': 1}
                    n['id'] = i['id']
                    n['name'] = i['properties']['CN']
                    n['EN'] = i['properties']['EN']
                    n['AN'] = i['properties']['AN']
                    n['Brief'] = i['properties']['Brief']
                    n['Symptom'] = i['properties']['Symptom']
                    n['Feature'] = i['properties']['Feature']
                    n['Lifestyle'] = i['properties']['Lifestyle']
                    n['Reason'] = i['properties']['Reason']
                    n['Method'] = i['properties']['Method']
                    for key in n.keys():
                        if (n[key] == ''):
                            n[key] = '无'
                    re['data']['nodes'].append(n)
                for i in range(1, len(re['data']['nodes'])):
                    l = {'source': 0, 'target': 0, 'name': '易患2', 'type': 'edge'}
                    l['target'] = i
                    re['data']['links'].append(l)
                re['data']['categories'].append({'name': '杂豆'})
                re['data']['categories'].append({'name': '虫害'})
            elif(type==2 or type==3 or type==4 or type==5 or type==6 or type==8 or type==9 or type==10 or type==11):
                search_result = graph.run(
                    "MATCH(n:`病害`) where n.CN = '" + entity + "' RETURN id(n) as id,properties(n) as properties").data()
                n = {'id': '', 'name': '', 'type': '', 'EN': '', 'AN': '', 'Brief': '', 'Symptom': '',
                     'Reason': '', 'Method': '', 'symbolSize': 70, 'category': 0}
                if(len(search_result)!=0):
                    n['id'] = search_result[0]['id']
                    n['name'] = search_result[0]['properties']['CN']
                    n['EN'] = search_result[0]['properties']['EN']
                    n['AN'] = search_result[0]['properties']['AN']
                    n['Brief'] = search_result[0]['properties']['Brief']
                    n['Symptom'] = search_result[0]['properties']['Symptom']
                    n['Reason'] = search_result[0]['properties']['Reason']
                    n['Method'] = search_result[0]['properties']['Method']
                    n['type']='disease'
                    re['data']['categories'].append({'name': '病害'})
                else:
                    search_result2 = graph.run(
                        "MATCH(n:`虫害`) where n.CN = '" + entity + "' RETURN id(n) as id,properties(n) as properties").data()
                    n['id'] = search_result2[0]['id']
                    n['name'] = search_result2[0]['properties']['CN']
                    n['EN'] = search_result2[0]['properties']['EN']
                    n['AN'] = search_result2[0]['properties']['AN']
                    n['Brief'] = search_result2[0]['properties']['Brief']
                    n['Symptom'] = search_result2[0]['properties']['Symptom']
                    n['Reason'] = search_result2[0]['properties']['Reason']
                    n['Method'] = search_result2[0]['properties']['Method']
                    n['type'] = 'insect'
                    print(search_result2)
                    n['Feature']= search_result2[0]['properties']['Feature']
                    n['Lifestyle'] = search_result2[0]['properties']['Lifestyle']
                    re['data']['categories'].append({'name': '虫害'})
                for key in n.keys():
                    if (n[key] == ''):
                        n[key] = '无'
                re['data']['nodes'].append(n)
                print(re['data']['nodes'])
            elif(type==7):
                search_result = graph.run(
                    "MATCH(n:`病原物`) where n.CN = '" + entity + "' RETURN id(n) as id,properties(n) as properties").data()
                n = {'id': '', 'name': '', 'type': 'pathongen', 'EN': '', 'ABBR': '', 'Infect': '', 'symbolSize': 70, 'category': 0}
                n['id'] = search_result[0]['id']
                n['name'] = search_result[0]['properties']['CN']
                n['EN'] = search_result[0]['properties']['EN']
                n['AN'] = search_result[0]['properties']['ABBR']
                n['Infect'] = search_result[0]['properties']['Infect']
                for key in n.keys():
                    if (n[key] == ''):
                        n[key] = '无'
                re['data']['nodes'].append(n)
                re['data']['categories'].append({'name': '病原物'})
            elif (type == 12):
                search_result = graph.run(
                    "MATCH(n:`病害`) where n.CN = '" + entity + "' RETURN id(n) as id,properties(n) as properties").data()
                n = {'id': '', 'name': '', 'type': 'disease', 'EN': '', 'AN': '', 'Brief': '', 'Symptom': '',
                     'Reason': '', 'Method': '', 'symbolSize': 70, 'category': 1}
                n['id'] = search_result[0]['id']
                n['name'] = search_result[0]['properties']['CN']
                n['EN'] = search_result[0]['properties']['EN']
                n['AN'] = search_result[0]['properties']['AN']
                n['Brief'] = search_result[0]['properties']['Brief']
                n['Symptom'] = search_result[0]['properties']['Symptom']
                n['Reason'] = search_result[0]['properties']['Reason']
                n['Method'] = search_result[0]['properties']['Method']
                for key in n.keys():
                    if (n[key] == ''):
                        n[key] = '无'
                re['data']['nodes'].append(n)
                search_result2 = graph.run(
                    "MATCH(n:`杂豆`)-[r:易患1]->(nn:`病害`) where nn.CN ='" + entity + "' RETURN id(n) as id,properties(n) as properties").data()
                for i in search_result2:
                    n={'id':'','name':'','type':'bean','symbolSize':50,'category':0}
                    n['id'] = i['id']
                    n['name'] = i['properties']['name']
                    re['data']['nodes'].append(n)
                for i in range(1, len(re['data']['nodes'])):
                    l = {'source': 0, 'target': 0, 'name': '易患1', 'type': 'edge'}
                    l['source'] = i
                    re['data']['links'].append(l)
                re['data']['categories'].append({'name': '杂豆'})
                re['data']['categories'].append({'name': '病害'})
            elif (type == 13):
                search_result = graph.run(
                    "MATCH(n:`虫害`) where n.CN = '" + entity + "' RETURN id(n) as id,properties(n) as properties").data()
                n = {'id': '', 'name': '', 'type': 'insect', 'EN': '', 'AN': '', 'Brief': '', 'Symptom': '',
                     'Feature':'','Lifestyle':'','Reason': '', 'Method': '', 'symbolSize': 70, 'category': 1}
                n['id'] = search_result[0]['id']
                n['name'] = search_result[0]['properties']['CN']
                n['EN'] = search_result[0]['properties']['EN']
                n['AN'] = search_result[0]['properties']['AN']
                n['Brief'] = search_result[0]['properties']['Brief']
                n['Symptom'] = search_result[0]['properties']['Symptom']
                n['Feature'] = search_result[0]['properties']['Feature']
                n['Lifestyle'] = search_result[0]['properties']['Lifestyle']
                n['Reason'] = search_result[0]['properties']['Reason']
                n['Method'] = search_result[0]['properties']['Method']
                for key in n.keys():
                    if (n[key] == ''):
                        n[key] = '无'
                re['data']['nodes'].append(n)
                search_result2 = graph.run(
                    "MATCH(n:`杂豆`)-[r:易患2]->(nn:`虫害`) where nn.CN = '" + entity + "' RETURN id(n) as id,properties(n) as properties").data()
                for i in search_result2:
                    n={'id':'','name':'','type':'bean','symbolSize':50,'category':0}
                    n['id'] = i['id']
                    n['name'] = i['properties']['name']
                    re['data']['nodes'].append(n)
                for i in range(1, len(re['data']['nodes'])):
                    l = {'source': 0, 'target': 0, 'name': '易患2', 'type': 'edge'}
                    l['source'] = i
                    re['data']['links'].append(l)
                re['data']['categories'].append({'name': '杂豆'})
                re['data']['categories'].append({'name': '虫害'})
            elif (type == 14):
                search_result = graph.run(
                    "MATCH(n:`病原物`) where n.CN = '" + entity + "' RETURN id(n) as id,properties(n) as properties").data()
                n = {'id': '', 'name': '', 'type': 'pathongen', 'EN': '', 'ABBR': '', 'Infect': '', 'symbolSize': 70,
                     'category': 0}
                n['id'] = search_result[0]['id']
                n['name'] = search_result[0]['properties']['CN']
                n['EN'] = search_result[0]['properties']['EN']
                n['AN'] = search_result[0]['properties']['ABBR']
                n['Infect'] = search_result[0]['properties']['Infect']
                for key in n.keys():
                    if (n[key] == ''):
                        n[key] = '无'
                re['data']['nodes'].append(n)
                search_result2 = graph.run(
                    "MATCH(n:`病原物`)-[r:导致]->(nn:`病害`) where n.CN = '" + entity + "' RETURN id(nn) as id,properties(nn) as properties").data()
                for i in search_result2:
                    n = {'id': '', 'name': '', 'type': 'disease', 'EN': '', 'AN': '', 'Brief': '', 'Symptom': '',
                         'Reason': '', 'Method': '', 'symbolSize': 50, 'category': 1}
                    n['id'] = i['id']
                    n['name'] = i['properties']['CN']
                    n['EN'] = i['properties']['EN']
                    n['AN'] = i['properties']['AN']
                    n['Brief'] = i['properties']['Brief']
                    n['Symptom'] = i['properties']['Symptom']
                    n['Reason'] = i['properties']['Reason']
                    n['Method'] = i['properties']['Method']
                    for key in n.keys():
                        if (n[key] == ''):
                            n[key] = '无'
                    re['data']['nodes'].append(n)
                for i in range(1, len(re['data']['nodes'])):
                    l = {'source': 0, 'target': 0, 'name': '导致', 'type': 'edge'}
                    l['target'] = i
                    re['data']['links'].append(l)
                re['data']['categories'].append({'name': '病原物'})
                re['data']['categories'].append({'name': '病害'})
            return HttpResponse(json.dumps(re))
# 图谱检索
def search(request):
    if (request.method == 'GET'):
        return render(request,"search.html")
    if (request.method == 'POST'):
        search_content=request.POST.get("search_content")
        re = {'code': 200, 'msg': 1, 'data': {'nodes': [], 'links': [], 'categories': []}}
        # 连接neo4j数据库，查询问题答案
        graph = Graph("http://localhost:7474", auth=("neo4j", "zwa20010718"))
        re['data']['categories'].append({'name': '杂豆'})
        re['data']['categories'].append({'name': '病害'})
        re['data']['categories'].append({'name': '虫害'})
        re['data']['categories'].append({'name': '病原物'})
        search_result = graph.run(
            "MATCH(n) where n.name contains '"+search_content+"' or n.CN contains '"+search_content+"' RETURN id(n) as id,properties(n) as properties,labels(n) as label").data()
        for i in search_result:
            if(i['label'][0]=='杂豆'):
                n = {'id': '', 'name': '', 'type': 'bean', 'symbolSize': 50, 'category': 0}
                n['id'] = i['id']
                n['name'] = i['properties']['name']
                re['data']['nodes'].append(n)
            elif(i['label'][0]=='病害'):
                n = {'id': '', 'name': '', 'type': 'disease', 'EN': '', 'AN': '', 'Brief': '', 'Symptom': '',
                     'Reason': '', 'Method': '', 'symbolSize': 50, 'category': 1}
                n['id'] = i['id']
                n['name'] = i['properties']['CN']
                n['EN'] = i['properties']['EN']
                n['AN'] = i['properties']['AN']
                n['Brief'] = i['properties']['Brief']
                n['Symptom'] = i['properties']['Symptom']
                n['Reason'] = i['properties']['Reason']
                n['Method'] = i['properties']['Method']
                for key in n.keys():
                    if (n[key] == ''):
                        n[key] = '无'
                re['data']['nodes'].append(n)
            elif (i['label'][0] == '虫害'):
                n = {'id': '', 'name': '', 'type': 'insect', 'EN': '', 'AN': '', 'Brief': '', 'Symptom': '',
                     'Feature': '', 'Lifestyle': '', 'Reason': '', 'Method': '', 'symbolSize': 50, 'category': 2}
                n['id'] = i['id']
                n['name'] = i['properties']['CN']
                n['EN'] = i['properties']['EN']
                n['AN'] = i['properties']['AN']
                n['Brief'] = i['properties']['Brief']
                n['Symptom'] = i['properties']['Symptom']
                n['Feature'] = i['properties']['Feature']
                n['Lifestyle'] = i['properties']['Lifestyle']
                n['Reason'] = i['properties']['Reason']
                n['Method'] = i['properties']['Method']
                for key in n.keys():
                    if (n[key] == ''):
                        n[key] = '无'
                re['data']['nodes'].append(n)
            elif (i['label'][0] == '病原物'):
                n = {'id': '', 'name': '', 'type': 'pathongen', 'EN': '', 'ABBR': '', 'Infect': '',
                     'symbolSize': 50, 'category': 3}
                n['id'] = i['id']
                n['name'] = i['properties']['CN']
                n['EN'] = i['properties']['EN']
                n['AN'] = i['properties']['ABBR']
                n['Infect'] = i['properties']['Infect']
                for key in n.keys():
                    if (n[key] == ''):
                        n[key] = '无'
                re['data']['nodes'].append(n)
        search_result2 = graph.run(
                    "MATCH (n)-[r]->(m) where n.name contains '"+search_content+"' or n.CN contains '"+search_content+"' or m.CN contains '"+search_content+"' RETURN id(n) as id1,type(r) as type,id(m) as id2").data()
        for i in search_result2:
            l = {'source': -1, 'target': -1, 'name': i['type'], 'type': 'edge'}
            if(search_content==''):
                l['source']=i['id1']
                l['target']=i['id2']
            else:
                flag1=0 # 标记id1节点是否已添加
                flag2=0 # 标记id2节点是否已添加
                n=len(re['data']['nodes'])
                for j in range(len(re['data']['nodes'])):
                    # print("re['data']['nodes']:" + str(re['data']['nodes'][j]['id']) + " id1:" + str(
                    #     i['id1']) + " id2:" + str(i['id2']))
                    if(re['data']['nodes'][j]['id']==i['id1']):
                        flag1=1
                        l['source']=j
                    if (re['data']['nodes'][j]['id'] == i['id2']):
                        flag2 = 1
                        l['target'] = j
                    if(flag1==1 and flag2==1):
                        break
                    # print("re['data']['nodes']:" + str(re['data']['nodes'][j]['id'])+" flag1:"+str(flag1)+" flag2:"+str(flag2))
                id=-1
                print("flag1:"+str(flag1)+" flag2:"+str(flag2))
                if(flag1==0):
                    id=i['id1']
                    l['source']=len(re['data']['nodes'])
                if(flag2==0):
                    id = i['id2']
                    l['target'] = len(re['data']['nodes'])
                if(flag1==0 or flag2==0):
                    search_result3 = graph.run(
                        "MATCH(n) where id(n) = " + str(id) + " RETURN id(n) as id,properties(n) as properties,labels(n) as label").data()
                    if (search_result3[0]['label'][0] == '杂豆'):
                        n = {'id': '', 'name': '', 'type': 'bean', 'symbolSize': 50, 'category': 0}
                        n['id'] = search_result3[0]['id']
                        n['name'] = search_result3[0]['properties']['name']
                        re['data']['nodes'].append(n)
                    elif (search_result3[0]['label'][0] == '病害'):
                        n = {'id': '', 'name': '', 'type': 'disease', 'EN': '', 'AN': '', 'Brief': '', 'Symptom': '',
                             'Reason': '', 'Method': '', 'symbolSize': 50, 'category': 1}
                        n['id'] = search_result3[0]['id']
                        n['name'] = search_result3[0]['properties']['CN']
                        n['EN'] = search_result3[0]['properties']['EN']
                        n['AN'] = search_result3[0]['properties']['AN']
                        n['Brief'] = search_result3[0]['properties']['Brief']
                        n['Symptom'] = search_result3[0]['properties']['Symptom']
                        n['Reason'] = search_result3[0]['properties']['Reason']
                        n['Method'] = search_result3[0]['properties']['Method']
                        for key in n.keys():
                            if (n[key] == ''):
                                n[key] = '无'
                        re['data']['nodes'].append(n)
                    elif (search_result3[0]['label'][0] == '虫害'):
                        n = {'id': '', 'name': '', 'type': 'insect', 'EN': '', 'AN': '', 'Brief': '', 'Symptom': '',
                             'Feature': '', 'Lifestyle': '', 'Reason': '', 'Method': '', 'symbolSize': 50, 'category': 2}
                        n['id'] = search_result3[0]['id']
                        n['name'] = search_result3[0]['properties']['CN']
                        n['EN'] = search_result3[0]['properties']['EN']
                        n['AN'] = search_result3[0]['properties']['AN']
                        n['Brief'] = search_result3[0]['properties']['Brief']
                        n['Symptom'] = search_result3[0]['properties']['Symptom']
                        n['Feature'] = search_result3[0]['properties']['Feature']
                        n['Lifestyle'] = search_result3[0]['properties']['Lifestyle']
                        n['Reason'] = search_result3[0]['properties']['Reason']
                        n['Method'] = search_result3[0]['properties']['Method']
                        for key in n.keys():
                            if (n[key] == ''):
                                n[key] = '无'
                        re['data']['nodes'].append(n)
                    elif (search_result3[0]['label'][0] == '病原物'):
                        n = {'id': '', 'name': '', 'type': 'pathongen', 'EN': '', 'ABBR': '', 'Infect': '',
                             'symbolSize': 50, 'category': 3}
                        n['id'] = search_result3[0]['id']
                        n['name'] = search_result3[0]['properties']['CN']
                        n['EN'] = search_result3[0]['properties']['EN']
                        n['AN'] = search_result3[0]['properties']['ABBR']
                        n['Infect'] = search_result3[0]['properties']['Infect']
                        for key in n.keys():
                            if (n[key] == ''):
                                n[key] = '无'
                        re['data']['nodes'].append(n)
            re['data']['links'].append(l)
        if(len(search_result)==0):
            re['msg']=0
        return HttpResponse(json.dumps(re))


